import pandas as pd

df = pd.read_csv('F:/25MCM_C/C_data/merged_olympics_data.csv')

df.columns = df.columns.str.strip().str.upper()

#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
df['GOLD'] = (df['MEDAL'] == 'Gold').astype(int)
df['SILVER'] = (df['MEDAL'] == 'Silver').astype(int)
df['BRONZE'] = (df['MEDAL'] == 'Bronze').astype(int)
df['TOTAL'] = df['GOLD'] + df['SILVER'] + df['BRONZE']

df['EVENT'] = df['EVENT'].str.strip()

country_event_summary = df.groupby(['NOC', 'EVENT']).agg({
    'GOLD': 'sum',
    'SILVER': 'sum',
    'BRONZE': 'sum',
    'TOTAL': 'sum'
}).reset_index()#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216

country_event_summary['GOLD_RATIO'] = country_event_summary['GOLD'] / country_event_summary['TOTAL']
country_event_summary['SILVER_RATIO'] = country_event_summary['SILVER'] / country_event_summary['TOTAL']
country_event_summary['BRONZE_RATIO'] = country_event_summary['BRONZE'] / country_event_summary['TOTAL']

country_event_summary['GOLD_SCORE'] = country_event_summary['GOLD'] * country_event_summary['GOLD_RATIO']
country_event_summary['SILVER_SCORE'] = country_event_summary['SILVER'] * country_event_summary['SILVER_RATIO']
country_event_summary['BRONZE_SCORE'] = country_event_summary['BRONZE'] * country_event_summary['BRONZE_RATIO']

top_events_by_country = []

for country in country_event_summary['NOC'].unique():
    country_data = country_event_summary[country_event_summary['NOC'] == country]

    # 对 [GOLD_SCORE, SILVER_SCORE, BRONZE_SCORE] 按行求和后排序
    sorted_events = country_data[['GOLD_SCORE', 'SILVER_SCORE', 'BRONZE_SCORE']] \
        .sum(axis=1) \
        .sort_values(ascending=False) \
        .head(3)
    # 比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
    top_3_indices = sorted_events.index

    event_names = country_data.loc[top_3_indices, 'EVENT'].tolist()

    top_events_by_country.append({
        'NOC': country,
        'Top 3 Events': event_names,
        'Total Importance Score': sorted_events.sum()
    })

top_events_df = pd.DataFrame(top_events_by_country)


print(top_events_df)

top_events_df.to_csv('F:/25MCM_C/C_data/top_important_events_by_country.csv', index=False)  # 保存为新的文件
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216